/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package ve.usb.cep.db.models;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

import ve.usb.cep.db.SelectObject;
import ve.usb.cep.db.SelectException;
import ve.usb.cep.db.Session;
import ve.usb.cep.db.domainObjects.Oferta;
import ve.usb.cep.db.domainObjects.Curso;
import ve.usb.cep.db.modelExceptions.OfertaException;
import ve.usb.cep.util.sqlWhere;

/**
 *
 * @author Andras
 */
public class OfertaModel{
    Connection conn;
    PreparedStatement rowCountStatement;

    public Oferta set(Oferta oferta)
        throws OfertaException
    {
        if (0 == oferta.getCodigo()){
            add(oferta);
        }else{
            update(oferta);
        }

        return oferta;
    }

    public Oferta add(Oferta oferta)
        throws OfertaException
    {

        conn = Session.connection();

        try{
            PreparedStatement pstmt = conn.prepareStatement("INSERT INTO oferta " +
                "(curso, ano, periodo, inscritos, abierto, dias)" +
                " VALUES (?, ?, ?, ?, ?, CAST(? AS dias))");

            pstmt.setInt(1, oferta.getCurso());
            pstmt.setString(2, oferta.getAno().toString());
            pstmt.setString(3, oferta.getPeriodo().toString());
            pstmt.setShort(4, oferta.getInscritos());
            pstmt.setShort(5, oferta.getAbierto());
            pstmt.setString(6, oferta.getDias());

            pstmt.executeUpdate();
        }catch(java.sql.SQLException e){
            e.printStackTrace();
            throw new OfertaException(e.getMessage());
        }
        return oferta;
    }

    public Oferta update(Oferta oferta)
        throws OfertaException
    {

        conn = Session.connection();

        try{
            PreparedStatement pstmt = conn.prepareStatement("UPDATE oferta " +
              "SET curso=?, ano=?, periodo=?, inscritos=?, abierto=?, dias=CAST(? AS dias) " +
              " WHERE codigo=?");

            pstmt.setInt(1, oferta.getCurso());
            pstmt.setString(2, oferta.getAno().toString());
            pstmt.setString(3, oferta.getPeriodo().toString());
            pstmt.setShort(4, oferta.getInscritos());
            pstmt.setShort(5, oferta.getAbierto());
            pstmt.setString(6, oferta.getDias());
            pstmt.setInt(7, oferta.getCodigo());
            pstmt.executeUpdate();
        }catch(java.sql.SQLException e){
            e.printStackTrace();
            throw new OfertaException(e.getMessage());
        }
        return oferta;
    }

    public void delete(int codigo)
        throws OfertaException
    {
        
        conn = Session.connection();
        try{
            PreparedStatement p = conn.prepareStatement("DELETE FROM oferta WHERE codigo=?");
            p.setInt(1, codigo);
            p.executeUpdate();
        }catch(SQLException e){
            e.printStackTrace();
            throw new OfertaException(e.getMessage());
        }
    }

    public Oferta getUnique(int curso, String periodo, String ano, String dias, int codigo)
        throws OfertaException
    {
        Oferta o = getByUnique(curso, periodo, ano, dias);
        if (null == o){
            return null;
        }
        if (o.getCodigo() == codigo){
            return null;
        }
        return o;
    }

    public Oferta getByUnique(int curso, String periodo, String ano, String dias)
       throws OfertaException
    {
        sqlWhere w = new sqlWhere();
        sqlWhere.rule[] c = new sqlWhere.rule[4];
        c[0] = w.new rule();
        c[0].setField("curso");
        c[0].setOp("eq");
        c[0].setData(Integer.toString(curso));
        c[1] = w.new rule();
        c[1].setField("periodo");
        c[1].setOp("eq");
        c[1].setData(periodo);
        c[2] = w.new rule();
        c[2].setField("ano");
        c[2].setOp("eq");
        c[2].setData(ano);
        c[3] = w.new rule();
        c[3].setField("dias");
        c[3].setOp("eq");
        c[3].setData(dias);
        w.setGroupOp(" AND ");
        w.setRules(c);
        
        ArrayList<Oferta> o = getCustom(new SelectObject((short) 1, (short) 1, "", "", w));
        if (0 == o.size()){
            return null;
        }else{
            return o.get(0);
        }
    }

    public ArrayList<Oferta> getCustom(SelectObject s) 
            throws OfertaException {

        conn = Session.connection();
        s.setConnection(conn);
        
        String[] of = Oferta.getFields(), ot = Oferta.getTypes(),
                 cf = Curso.getFields(), ct = Curso.getTypes(),
                 fields = new String[of.length * 2 + cf.length],
                 types = new String[fields.length];
        int i = 0;
        for (String f : of){
            fields[i ++] = f;
            fields[i ++] = "oferta." + f;
        }
        for (String f : cf){
            fields[i ++] = "curso." + f;
        }
        i = 0;
        for (String t : ot){
            types[i ++] = t;
            types[i ++] = t;
        }
        for (String t : ct){
            types[i ++] = t;
        }
        s.setFields(fields);
        s.setTypes(types);
        s.setTable("oferta");
        s.setJoin("curso", "curso", "curso.codigo");

        if ("curso".equals(s.getSidx())){
            s.setSidx("nombre");
        }

        PreparedStatement p;
        try{
            p = s.getSearchQuery();
            rowCountStatement = s.getCountQuery();
        }catch(SelectException e){
            e.printStackTrace();
            throw new OfertaException(e.getMessage());
        }

        ArrayList<Oferta> arr = new ArrayList<Oferta>();
        try{
            ResultSet rs = p.executeQuery();
            while (rs.next()){
                arr.add(new Oferta(
                    rs.getInt("codigo"),
                    rs.getInt("curso"),
                    rs.getString("ano"),
                    rs.getString("periodo"),
                    rs.getShort("inscritos"),
                    rs.getShort("abierto"),
                    rs.getString("dias")
                ));
            }
        }catch(SQLException e){
            e.printStackTrace();
            throw new OfertaException(e.getMessage());
        }

        return arr;
    }

    public int getRowCount()
        throws OfertaException
    {
        try{
            ResultSet rs = rowCountStatement.executeQuery();
            rs.next();
            return rs.getInt(1);
        }catch(SQLException e){
            e.printStackTrace();
            throw new OfertaException(e.getMessage());
        }
    }
}
